package sql;

import excel.CanTingDto;
import excel.DwbiDto;
import excel.DwbiDtoMenuInfo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * todo
 *
 * @author zhengshan
 * @Date 2023/1/3 9:52
 */
public class MysqlDemo {
    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL

    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    Connection conn = null;
    Statement stmt = null;
    final String DB_URL = "jdbc:mysql://139.129.92.132:3306/balabala?useSSL=false&serverTimezone=UTC";


    // 数据库的用户名与密码，需要根据自己的设置
    final String USER = "root";
    final String PASS = "root123456";

    public MysqlDemo() {
        try {
            Class.forName(JDBC_DRIVER);
            System.out.println("加载数据库驱动成功");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("连接数据库驱动成功");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void Test(){
        System.out.println("加载数据库驱动成功");
    }

    public void insert(String str){
        String sql1 = "insert into sheetone(esp_brand,product_fg_code,dwbi_brand,dwbi_product_code,product_name) values(" + str + ")";
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();
            System.out.println("插入成功！");

            System.out.println("插入结束！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void insertTwo(String str){
        String sql1 = "insert into menukfc(menu_code,menu_name,brand_code) values(" + str + ")";
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();
            System.out.println("插入成功！");

            System.out.println("插入结束！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void insertDiningInfo(List<CanTingDto> canTingDtos){
        StringBuffer sql = new StringBuffer();
        sql.append("insert into material_all_dining (brand_code, brand_descr, jde_code, jde_name, BASICMATERIAL_CODE, BASICMATERIAL_NAME, RECIPE_UNIT_CODE, unit_name, BASICMATERIAL_TYPE_CODE, BASICMATERIAL_TYPE) value ");
        for(CanTingDto canTingDto : canTingDtos){
            sql.append(canTingDto.toString());
        }
        String sqls = sql.substring(0, sql.length() - 1);
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sqls);
            ps.executeUpdate();
            System.out.println("插入成功！");

            System.out.println("插入结束！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println(e.getMessage());
        }

    }

    public void insertTestDiningInfo(String name){
        StringBuffer sql = new StringBuffer();
        sql.append("insert into testonesname (name) values (" + name +")");

        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql.toString());
            ps.executeUpdate();
            System.out.println("插入成功！");

            System.out.println("插入结束！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println(e.getMessage());
        }

    }

    public void insertTestDining20230309Info(String str){
        StringBuffer sql = new StringBuffer();
        /**
         brand;
         itemCode;
         standardProductCode;
         e;
         subclassdesc;
         classdesc;
         subcategorydesc;
         categorydesc;
         */
        sql.append("insert into RESULT20230418 (brand, itemCode, standardProductCode, e, subclassdesc, classdesc, subcategorydesc, categorydesc) values " + str + ";");

        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql.toString());
            ps.executeUpdate();
            System.out.println("插入成功！");

            System.out.println("插入结束！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println(e.getMessage());
        }

    }

    public void insertTestDining794Info(String str){
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO product_standard_20230228 (item_code, standard_product_code, real_item_name, standard_product_name)  VALUES " + str + ";");

        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql.toString());
            ps.executeUpdate();
            System.out.println("插入成功！");

            System.out.println("插入结束！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println(e.getMessage());
        }

    }

    public void insertMenu(String str){
        String sql1 = "insert into sheet_left_menu(esp_brand, product_fg_code, dwbi_brand, dwbi_product_code, product_name, mc_brand, mc_itemcode, s_product_code, mc_name, menu_code, menu_name, MENU_BRAND) values(" + str + ")";
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();
            System.out.println("插入成功！");

            System.out.println("插入结束！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println(e.getMessage());
        }
    }

    public List<DwbiDtoMenuInfo> querySheetInfo(){
        try {
            List<DwbiDtoMenuInfo> dwbiDtos = new ArrayList<>();
            // 3.创建一个Statement对象
            stmt = conn.createStatement();
            String sql = "select * from (" +
                    "SELECT DISTINCT" +
                    "`one`.*," +
                    "`menu`.`MENU_CODE` AS `MENU_CODE`," +
                    "`menu`.`MENU_NAME` AS `MENU_NAME`," +
                    "`menu`.`BRAND_CODE` AS `MENU_BRAND` " +
                    "FROM `sheet_one_two_all_kfc` `one`" +
                    "LEFT JOIN `yum_sp_bom_products` `pro` ON `one`.`product_fg_code` = `pro`.`SP_PRODUCT_CODE`" +
                    "LEFT JOIN `hmdm_yum_bom_menus` `menu` ON `pro`.`MENU_ID` = `menu`.`MENU_ID`" +
                    ") t " +
                    "WHERE t.menu_code is not null";
            // 4.execute()方法执行SQL，得到一个ResultSet结果集
            ResultSet rs = stmt.executeQuery(sql);
            // 5.通过遍历ResultSet结果集得到数据，给POJO赋值处理数据，最终返回POJO
            while (rs.next()) {
                DwbiDtoMenuInfo dwbiDto = new DwbiDtoMenuInfo();
                String espBrand = rs.getString("esp_brand");
                String productFgCode = rs.getString("product_fg_code");
                String dwbiBrand = rs.getString("dwbi_brand");
                String dwbiProductCode = rs.getString("dwbi_product_code");
                String productName = rs.getString("product_name");

                String mcBrand = rs.getString("mc_brand");
                String mcItemcode = rs.getString("mc_itemcode");
                String sProductcode = rs.getString("s_productcode");
                String mcName = rs.getString("mc_name");

                String menuCode = rs.getString("menu_code");
                String menuName = rs.getString("menu_name");
                String menuBrand = rs.getString("menu_brand");


                dwbiDto.setEspBrand(espBrand);
                dwbiDto.setProductFgCode(productFgCode);
                dwbiDto.setDwbiBrand(dwbiBrand);
                dwbiDto.setDwbiProductCode(dwbiProductCode);
                dwbiDto.setProductName(productName);

                dwbiDto.setMcBrand(mcBrand);
                dwbiDto.setMcItemcode(mcItemcode);
                dwbiDto.setsProductCode(sProductcode);
                dwbiDto.setMcName(mcName);

                dwbiDto.setMenuCode(menuCode);
                dwbiDto.setMenuName(menuName);
                dwbiDto.setMenuBrand(menuBrand);
                dwbiDtos.add(dwbiDto);
            }

            // 6.关闭数据库相关资源，包括ResultSet、Statement、Connection
//            rs.close();
//            stmt.close();
//            conn.close();
            return dwbiDtos;
        }catch (Exception e){

        }
        return null;
    }

}
